<?php
require 'lib.php';
$server = "172.21.147.31";
$connectionInfo = array( "Database"=>"ss2g6", "UID"=>"ss2g6", "PWD"=>"group6",  'ReturnDatesAsStrings'=> true  );
$conn = sqlsrv_connect( $server, $connectionInfo );

if ($conn == false){
	 die( print_r( sqlsrv_errors(), true));
}

$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );

if(isset($_GET['update'])){
    $updatePharmacyName = $_GET['pharmacyName'];
    $sql = "SELECT * FROM Pharmacy WHERE pharmacyName = '$updatePharmacyName'";
    $result = sqlsrv_query( $conn, $sql, $params, $options );
    $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);

    $updatePharmacyAddress = $row['address'];
    $updatePharmacyPhoneNum = $row['phoneNum'];
}

if(isset($_POST['Update'])){
    $updatePharmacyNameOld = $_POST['pharmacyNameOld'];
    $updatePharmacyName = $_POST['pharmacyName'];
    $updatePharmacyAddress = $_POST['address'];
    $updatePharmacyPhoneNum = $_POST['phoneNum'];
    
    $sql = "UPDATE Pharmacy SET pharmacyName = '$updatePharmacyName', address = '$updatePharmacyAddress', phoneNum = '$updatePharmacyPhoneNum' WHERE pharmacyName = '$updatePharmacyNameOld'";
    $result = sqlsrv_query( $conn, $sql, $params, $options );
    if($result === false){
        if(($errors = sqlsrv_errors()) != null){
            $error_msg = "";
            foreach($errors as $error){
                $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
            }
        }
    }else{
        $confirm = "Pharmacy information updated.";
    }
}

if(isset($_POST['Insert'])) {
    $pharmacyName = $_POST['pharmacyName'];
    $address = $_POST['address'];
    $phoneNum = $_POST['phoneNum'];    
	
	/* check empty fields */
	if($phoneNum == "" || $pharmacyName == "" || $address == ""){
		$validation_error = true;
	}
	else {
		$validation_error = false;
	}
	
	/*check whether phoneNum is a numeric number */
	if(is_numeric($phoneNum)){
		$numeric = true;
	}
	else {
		$numeric = false;
	} 
    $sql = "INSERT INTO Pharmacy VALUES ('$pharmacyName', '$address', '$phoneNum', 0)";
	
	if($validation_error == false && $numeric == true){
    $params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $result = sqlsrv_query( $conn, $sql, $params, $options );
        if($result === false){
            if(($errors = sqlsrv_errors()) != null){
                $error_msg = "";
                foreach($errors as $error){
                    $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
                }
            }
        }
	    else {
            $confirm = "The Pharmacy information is inserted.";
	    }
    }
}

if(isset($_GET['delete'])){
	$pharmacyName = $_GET['pharmacyName'];
	$sql = "DELETE FROM Pharmacy WHERE pharmacyName = '". $pharmacyName ."'";
    $result = sqlsrv_query( $conn, $sql, $params, $options );
	
    if($result === false){
        if(($errors = sqlsrv_errors()) != null){
            $error_msg = "";
            foreach($errors as $error){
                $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
            }
        }
    }
    else{
		$error = false;
    }
}

if (isset($_POST['submit'])){
    $sql1 = $_POST['query'];
}
else{
    $sql = "SELECT * FROM Pharmacy";
}

$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );
?>

<? include 'html_head.php'; ?>
    <? include 'header.php'; ?>
	
	<div class="container-fluid">
      <div class="row-fluid">
        <div class="span2">
            <? include 'sidebar.php'; ?>
        </div><!--/span-->
        <div class="span9">
        <h2>Pharmacy</h2>	
	    <hr>
		<?
		if(isset($_GET['delete'])){
			if (isset($error_msg)){ ?>
				<div class="alert alert-error">
                    <? echo $error_msg; ?> 
				</div>
				
			<? 
			} 
			else { ?>
				<div class="alert alert-success">
				<? echo "Data deleted successfully."?>
				</div>
				
			<?
			} 
		}?>


        <? if(isset($_POST['Update'])){ 
                if(isset($error_msg)){
        ?>
			    <div class="alert alert-error">
				    <? echo $error_msg; ?>
				</div>
        <?  }else{ ?>
			    <div class="alert alert-success">
				    <? echo $confirm; ?>
				</div>
        <? }
        } ?>
		<?
			if(isset($_POST['Insert'])){
				$msg = "";
				if($numeric == false){ 
					$msg = "Phone number's text field must be an integer value <br />";
				}
				if($validation_error){
					$msg = $msg . "No text fields can be empty";
				}
				if(isset($error_msg)){
					$msg = "Data cannot be inserted, foreign key constraints or triggers violated <br />";
				}
				
				if ($msg <> ""){ ?>
					<div class="alert alert-error">
						<? echo $msg; ?>
					</div>
                <?
                
                }
                elseif(isset($error_msg)){ ?>
					<div class="alert alert-error">
						<? echo $error_msg; ?>
					</div>

                <?}
                else { ?>
					<div class="alert alert-success">
					<? echo $confirm;?>
					</div>
					
				<?
				} 
			}?>
         <?
        
		if(sqlsrv_has_rows($stmt))
		{		
			$row_count = sqlsrv_num_rows($stmt);
            $colnames = sqlsrv_field_metadata($stmt)
		?>
		
		<table class="table table-striped table table-condensed">
		
		<thead>
			<tr>
			 <?
                    echo "<th>Pharmacy Name</th>";
					echo "<th>Address</th>";
					echo "<th>Phone Number</th>";
					echo "<th>Rip Off</th>";
                ?>
			</tr>	
			</thead>
		<tbody>
		<?
			while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
        ?>
			<tr>
                <?
                    echo "<td>" . $row['pharmacyName'] . "</td>";
                    echo "<td>" . $row['address'] . "</td>";
                    echo "<td>" . $row['phoneNum'] . "</td>";
                    echo "<td>";
                    if ($row['pharmacyName'] == 1){
                        echo "Yes";
                    }
                    else{
                        echo "No";
                    }
                    echo "</td>";

					$pharmacyName = urlencode($row['pharmacyName']);
					echo "<td><a href='pharmacy.php?delete=true&pharmacyName=" . $pharmacyName . "'>Delete</a></td>";
					echo "<td><a href='pharmacy.php?update=true&pharmacyName=" . $pharmacyName . "#updates'>Update</a></td>";
				?>
			</tr>
		  
		<?
			}
		?>
		</tbody>
		</table>
		<?
		}
		else{
			echo "<h2>No records.</h2>";
		}
		?>
          <div class="row-fluid">
            <div class="span12">

            <? if (isset($_GET['update'])){ ?>
            <div>
             <a name="updates"></a><h3>Update Pharmacy information:</h3> 
                    <form method="post" action="pharmacy.php" class="well form-horizontal">
                    <input type="hidden" name="pharmacyNameOld" value="<? echo $updatePharmacyName ?>" />
                     <div class="control-group">
                        <label class="control-label">Pharmacy Name</label>
                        <div class="controls">
                        <input type="text" name="pharmacyName" id="pharmacyName" value="<? echo $updatePharmacyName; ?>" />
                        </div> 
                    </div>
                    <div class="control-group">
                        <label class="control-label">Address</label>
                        <div class="controls">
                            <input type="text" name="address" id="address" value="<? echo $updatePharmacyAddress; ?>"/>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Phone Number</label>
                        <div class="controls">
                            <input type="text" name="phoneNum" id="phoneNum" value="<? echo $updatePharmacyPhoneNum; ?>"/>
                        </div>
                    </div>
                    <div class="form-actions">
                        <input type="submit" name="Update" value="Update"  class="btn btn-primary"/>
                    </div> 
                    </form>   
            </div>
            <? } ?>


            <h3>Create a new Pharmacy information:</h3> 
                    <form method="post" action="pharmacy.php" class="well form-horizontal">
                     <div class="control-group">
                        <label class="control-label">Pharmacy Name</label>
                        <div class="controls">
                            <input type="text" name="pharmacyName" id="pharmacyName" placeholder="eg. Smith Pharmacy" />
                        </div> 
                    </div>
                    <div class="control-group">
                        <label class="control-label">Address</label>
                        <div class="controls">
                            <input type="text" name="address" id="address" placeholder="eg. 150 Orchard Rd #05-37 Singapore 238841" />
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Phone Number</label>
                        <div class="controls">
                            <input type="text" name="phoneNum" id="phoneNum" placeholder="eg. 61234567" />
                        </div>
                    </div>                  
                    <div class="form-actions">
                        <input type="submit" name="Insert" value="Insert"  class="btn btn-primary"/>
                    </div> 
                    </form>
              <h3>Enter your query:</h3>
                    <form method="post" action="custom.php" class="well form-inline">
                    <div class="control-group">
                        <div class="controls">
                            <textarea name="query" class="span10" id="textarea" rows="5" placeholder = 'SQL query'></textarea>
                        </div>
                    </div>
                    <p>
                    <p><input type="submit" name="submit" class="btn btn-primary" href="#" /></p>
                    </form>
                    
            </div><!--/span-->
          </div><!--/row-->
        </div><!--/span-->
      </div><!--/row-->

<? include 'html_tail.php'; ?>
